package zqtools;

import com.thoughtworks.xstream.XStream;
import com.thoughtworks.xstream.io.xml.DomDriver;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import zqdb.O_DOFIVE;
import zqdb.O_INFO;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
import java.util.logging.Logger;

public class DBUtil {

    private static String URL = "";
    private static String USER = "";
    private static String PASS = "";
//    private static Connection conn = null;

    static {
        try {
            ClassLoader classLoader = DBUtil.class.getClassLoader();
            InputStream resourceAsStream = classLoader.getResourceAsStream("info.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            URL = properties.getProperty("url");
            USER = properties.getProperty("user");
            PASS = properties.getProperty("pass");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //对外提供一个方法来获取数据库连接
    public static Connection getConn() {
        Connection conn = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            //2.获得数据库的连接
            conn = DriverManager.getConnection(URL, USER, PASS);
        } catch (ClassNotFoundException | SQLException e) {
              throw new RuntimeException(e);
        }

        return conn;
    }

    public static JSONObject f_queryGz(O_INFO of) {
        JSONObject ret = new JSONObject();
        XStream xStream = new XStream(new DomDriver());
        //设置T_FORM类的别名
        xStream.alias("sysinfo", O_INFO.class);
        //将Java对象序列化成XML
        String ls_xml = xStream.toXML(of);
        ls_xml = "<?xml version=\"1.0\" encoding=\"gb2312\"?>\n" + ls_xml;
        String ls_sql = "{call WP_PCGZ(?)}";
        Connection conn = DBUtil.getConn();
        CallableStatement callsm = null;
        try {
            callsm = conn.prepareCall(ls_sql);
            //给存储过程传参
            callsm.setString(1, ls_xml);
            //执行存储过程
            callsm.execute();

            int fl = 1;
            //获取结果集
            ResultSet rs1 = callsm.getResultSet();
            int columnCount = callsm.getResultSet().getMetaData().getColumnCount();
            JSONArray arr = new JSONArray();
            while (rs1.next()) {
                JSONObject j = new JSONObject();
                for (int i = 0; i < columnCount; i++) {
                    j.put("VALUE" + String.format("%02d", i + 1), rs1.getString(i + 1));
                }
                arr.add(j);
            }
            ret.put("db" + fl, arr);

            while (callsm.getMoreResults()) {
                //获取结果集
                rs1 = callsm.getResultSet();
                columnCount = callsm.getResultSet().getMetaData().getColumnCount();

                arr = new JSONArray();
                while (rs1.next()) {
                    JSONObject j = new JSONObject();
                    for (int i = 0; i < columnCount; i++) {
                        j.put("VALUE" + String.format("%02d", i + 1), rs1.getString(i + 1));
                    }
                    arr.add(j);
                }
                fl++;
                ret.put("db" + fl, arr);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                callsm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
//                log.error("exception message", e);
//                log.error(e.getMessage());// 只能输出简短的错误信息，不便于排错
            }
        }

        return ret;
    }

    public static JSONObject f_query(O_INFO of) {
        JSONObject ret = new JSONObject();
        XStream xStream = new XStream(new DomDriver());
        //设置T_FORM类的别名
        xStream.alias("sysinfo", O_INFO.class);
        //将Java对象序列化成XML
        String ls_xml = xStream.toXML(of);
        ls_xml = "<?xml version=\"1.0\" encoding=\"gb2312\"?>\n" + ls_xml;
        String ls_sql = "{call WP_PC(?)}";
        Connection conn = DBUtil.getConn();
        CallableStatement callsm = null;
        try {
            callsm = conn.prepareCall(ls_sql);
            //给存储过程传参
            callsm.setString(1, ls_xml);
            //执行存储过程
            callsm.execute();

            int fl = 1;
            //获取结果集
            ResultSet rs1 = callsm.getResultSet();
            int columnCount = callsm.getResultSet().getMetaData().getColumnCount();
            JSONArray arr = new JSONArray();
            while (rs1.next()) {
                JSONObject j = new JSONObject();
                for (int i = 0; i < columnCount; i++) {
                    j.put("VALUE" + String.format("%02d", i + 1), rs1.getString(i + 1));
                }
                arr.add(j);
            }
            ret.put("db" + fl, arr);

            while (callsm.getMoreResults()) {
                //获取结果集
                rs1 = callsm.getResultSet();
                columnCount = callsm.getResultSet().getMetaData().getColumnCount();

                arr = new JSONArray();
                while (rs1.next()) {
                    JSONObject j = new JSONObject();
                    for (int i = 0; i < columnCount; i++) {
                        j.put("VALUE" + String.format("%02d", i + 1), rs1.getString(i + 1));
                    }
                    arr.add(j);
                }
                fl++;
                ret.put("db" + fl, arr);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                callsm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return ret;
    }

    public static JSONObject f_query2(O_INFO of) {
        JSONObject ret = new JSONObject();
        XStream xStream = new XStream(new DomDriver());
        //设置T_FORM类的别名
        xStream.alias("sysinfo", O_INFO.class);
        //将Java对象序列化成XML
        String ls_xml = xStream.toXML(of);
        ls_xml = "<?xml version=\"1.0\" encoding=\"gb2312\"?>\n" + ls_xml;
        String ls_sql = "{call WP_PC2(?)}";
        Connection conn = DBUtil.getConn();
        CallableStatement callsm = null;
        try {
            callsm = conn.prepareCall(ls_sql);
            //给存储过程传参
            callsm.setString(1, ls_xml);
            //执行存储过程
            callsm.execute();

            int fl = 1;
            //获取结果集
            ResultSet rs1 = callsm.getResultSet();
            int columnCount = callsm.getResultSet().getMetaData().getColumnCount();
            JSONArray arr = new JSONArray();
            while (rs1.next()) {
                JSONObject j = new JSONObject();
                for (int i = 0; i < columnCount; i++) {
                    j.put("VALUE" + String.format("%02d", i + 1), rs1.getString(i + 1));
                }
                arr.add(j);
            }
            ret.put("db" + fl, arr);

            while (callsm.getMoreResults()) {
                //获取结果集
                rs1 = callsm.getResultSet();
                columnCount = callsm.getResultSet().getMetaData().getColumnCount();

                arr = new JSONArray();
                while (rs1.next()) {
                    JSONObject j = new JSONObject();
                    for (int i = 0; i < columnCount; i++) {
                        j.put("VALUE" + String.format("%02d", i + 1), rs1.getString(i + 1));
                    }
                    arr.add(j);
                }
                fl++;
                ret.put("db" + fl, arr);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                callsm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return ret;
    }

    public static JSONObject f_hy(O_INFO of) {
        JSONObject ret = new JSONObject();
        XStream xStream = new XStream(new DomDriver());
        //设置T_FORM类的别名
        xStream.alias("sysinfo", O_INFO.class);
        //将Java对象序列化成XML
        String ls_xml = xStream.toXML(of);
        ls_xml = "<?xml version=\"1.0\" encoding=\"gb2312\"?>\n" + ls_xml;
        String ls_sql = "{call WP_HY(?)}";
        Connection conn = DBUtil.getConn();
        CallableStatement callsm = null;
        try {
            callsm = conn.prepareCall(ls_sql);
            //给存储过程传参
            callsm.setString(1, ls_xml);
            //执行存储过程
            callsm.execute();

            int fl = 1;
            //获取结果集
            ResultSet rs1 = callsm.getResultSet();
            int columnCount = callsm.getResultSet().getMetaData().getColumnCount();
            JSONArray arr = new JSONArray();
            while (rs1.next()) {
                JSONObject j = new JSONObject();
                for (int i = 0; i < columnCount; i++) {
                    j.put("VALUE" + String.format("%02d", i + 1), rs1.getString(i + 1));
                }
                arr.add(j);
            }
            ret.put("db" + fl, arr);

            while (callsm.getMoreResults()) {
                //获取结果集
                rs1 = callsm.getResultSet();
                columnCount = callsm.getResultSet().getMetaData().getColumnCount();

                arr = new JSONArray();
                while (rs1.next()) {
                    JSONObject j = new JSONObject();
                    for (int i = 0; i < columnCount; i++) {
                        j.put("VALUE" + String.format("%02d", i + 1), rs1.getString(i + 1));
                    }
                    arr.add(j);
                }
                fl++;
                ret.put("db" + fl, arr);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                callsm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return ret;
    }

    public static JSONObject f_hy(JSONObject of) {
        JSONObject ret = new JSONObject();
        String ls_sql = "{call WP_HY(?)}";
        Connection conn = DBUtil.getConn();
        CallableStatement callsm = null;

        try {
            callsm = conn.prepareCall(ls_sql);
            //给存储过程传参
            callsm.setString(1, of.toString());
            //执行存储过程
            callsm.execute();

            //获取结果集
            JSONArray arr = new JSONArray();
            ResultSet rs1 = callsm.getResultSet();
            int columnCount = callsm.getResultSet().getMetaData().getColumnCount();
            if (columnCount > 0) {
                ret.put("CODE", "1");
                ret.put("MSG", "操作成功");
                while (rs1.next()) {
                    JSONObject j = new JSONObject();
                    for (int i = 0; i < columnCount; i++) {
                        String ls_colName = callsm.getResultSet().getMetaData().getColumnName(i + 1);
                        j.put(ls_colName, rs1.getString(i + 1));
                    }
                    arr.add(j);
                }
                ret.put("DATA", arr);
            } else {
                ret.put("CODE", "0");
                ret.put("MSG", "无返回信息");
                ret.put("DATA", arr);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                callsm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return ret;
    }

    public static JSONObject f_hy2(JSONObject of) {
        JSONObject ret = new JSONObject();
        String ls_sql = "{call WP_HY2(?)}";
        Connection conn = DBUtil.getConn();
        CallableStatement callsm = null;

        try {
            callsm = conn.prepareCall(ls_sql);
            //给存储过程传参
            callsm.setString(1, of.toString());
            //执行存储过程
            callsm.execute();

            //获取结果集
            JSONArray arr = new JSONArray();
            ResultSet rs1 = callsm.getResultSet();
            int columnCount = callsm.getResultSet().getMetaData().getColumnCount();
            if (columnCount > 0) {
                ret.put("CODE", "1");
                ret.put("MSG", "操作成功");
                while (rs1.next()) {
                    JSONObject j = new JSONObject();
                    for (int i = 0; i < columnCount; i++) {
                        String ls_colName = callsm.getResultSet().getMetaData().getColumnName(i + 1);
                        j.put(ls_colName, rs1.getString(i + 1));
                    }
                    arr.add(j);
                }
                ret.put("DATA", arr);
            } else {
                ret.put("CODE", "0");
                ret.put("MSG", "无返回信息");
                ret.put("DATA", arr);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                callsm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return ret;
    }

    public static JSONObject f_dofive(O_DOFIVE of) {
        JSONObject ret = new JSONObject();
        XStream xStream = new XStream(new DomDriver());
        //设置T_FORM类的别名
        xStream.alias("sysinfo", O_DOFIVE.class);
        //将Java对象序列化成XML
        String ls_xml = xStream.toXML(of);
        ls_xml = "<?xml version=\"1.0\" encoding=\"gb2312\"?>\n" + ls_xml;
        //System.out.println(ls_xml);
        String ls_sql = "{call WP_DOFIVE(?)}";
        Connection conn = DBUtil.getConn();
        CallableStatement callsm = null;
        try {
            callsm = conn.prepareCall(ls_sql);
            //给存储过程传参
            callsm.setString(1, ls_xml);
            //执行存储过程
            callsm.execute();

            //获取结果集
            JSONArray arr = new JSONArray();
            ResultSet rs1 = callsm.getResultSet();
//            rs1 = callsm.getResultSet();
//            while (rs1.next()) {
//                JSONObject j = new JSONObject();
//                for (int i = 0; i < columnCount; i++) {
//                    j.put("VALUE" + String.format("%02d", i + 1), rs1.getString(i + 1));
//                }
//                arr.add(j);
//            }

            int columnCount = callsm.getResultSet().getMetaData().getColumnCount();
            if (columnCount > 0) {
                ret.put("code", "1");
                ret.put("msg", "操作成功");
                while (rs1.next()) {
                    JSONObject j = new JSONObject();
                    for (int i = 0; i < columnCount; i++) {
                        String ls_colName = callsm.getResultSet().getMetaData().getColumnName(i + 1);
                        j.put(ls_colName, rs1.getString(i + 1));
                    }
                    arr.add(j);
                }
                ret.put("data", arr);
            } else {
                ret.put("code", "0");
                ret.put("msg", "无返回信息");
                ret.put("data", arr);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                callsm.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return ret;
    }

}


